<?php
/* lib for the GraphSpace, Graph Interpreter Project: nth-graph. 
 * @copyright Copyright (c) 2009-2010 Nate Morse
 * nthQueryGraph class version 0.03
 */
 
class nthQueryGraph extends nthGraph {
    var $rdbms = false;
    var $db = false;
    // moved to the parent ... var $environment;
    var $current_node_index;
    var $visited_list;
    var $selectClause;
    var $fromClause;
    var $whereClause;
    var $orderbyClause;

    //constructor
    function __construct($graphName, $env) {
        //echo $graphName;
        parent::__construct($graphName);
        if (!$rdbms) {
            // Connected to MySQL
            $this->rdbms = mysql_connect("h50mysql9.secureserver.net", "nthgraphsite", "ceDar5oRt") or die(mysql_error());
        }
        if (!$db) {
            // Connected to Database
            $this->db = mysql_select_db("nthgraphsite") or die(mysql_error());
        }
        $this->environment = $env;
        $this->debug_data("qg environment ", $env);

    }
    
	function debug($message) {
		//echo '<br/>debug QG: '.$message;
	}

	function debug_data($message, $d) {
		//echo '<br/>debug QG: '.$message; echo "<pre>"; var_dump($d); echo "</pre>";
	}
        
    function query() {    	
    	if ($this->graph['graph']['version'] != '0.03') {
    		$this->warn(" <br/> warning qurey graph is not of correct version ");
    	}
    	if (!array_key_exists('subtype', $this->graph['graph']) || $this->graph['graph']['subtype'] === 'select') {
        	$this->setInputs();
        	$this->current_node_index = $this->getStartNodeIndex();
            $this->selectClause = array();
            $this->fromClause = array();
            $this->whereClause = array();
            $this->orderbyClause = array();
        	$this->visited_list = array();
            $this->nodeToSqlSelectQuery($this->current_node_index);
            $rs = $this->retrieveData();
        	return $rs;
    	}
    	if ($this->graph['graph']['subtype'] === 'update') {
        	$this->setInputs();
        	$this->current_node_index = $this->getStartNodeIndex();
            $this->setClause = array();
            $this->whereClause = array();
        	$this->visited_list = array();
            $this->nodeToSqlUpdateQuery($this->current_node_index);
            $status = $this->executeUpdate();
        	return $status;
    	}
    	if ($this->graph['graph']['subtype'] === 'insert') {
        	$this->setInputs();
        	$this->current_node_index = $this->getStartNodeIndex();
            $this->setClause = array();
        	$this->visited_list = array();
            $this->nodeToSqlInsertQuery($this->current_node_index);
            $status = $this->executeInsert();
        	return $status;
    	}
    	if ($this->graph['graph']['subtype'] === 'delete') {
        	$this->setInputs();
        	$this->current_node_index = $this->getStartNodeIndex();
            $this->whereClause = array();
        	$this->visited_list = array();
            $this->nodeToSqlDeleteQuery($this->current_node_index);
            $status = $this->executeDelete();
        	return $status;
    	}
    }


	function setInputs() {
		foreach($this->graph['nodes'] as $node_index=>$node_obj) {
			if (array_key_exists('type', $node_obj) && $node_obj['type'] == 'in') {
				if (array_key_exists('subtype', $node_obj) && $node_obj['subtype'] == 'INT') {				    
					$this->graph['nodes'][$node_index]['data'][$node_obj['name']] = (INT) $this->environment[$node_obj['name']];
				}
				if (array_key_exists('subtype', $node_obj) && $node_obj['subtype'] == 'STR') {
				    $this->debug_data("input string for ", $node_obj['name']);
                    $raw_str = $this->environment[$node_obj['name']];
				    $this->debug("input string (raw) ", $raw_str);
                    $refined_str = mysql_real_escape_string($raw_str);			    
					$this->graph['nodes'][$node_index]['data'][$node_obj['name']] = $refined_str;
				}
			}
		}
	}
    
    private function nodeToSqlSelectQuery($node_index) {
        if (!in_array($node_index, $this->visited_list)
        && $this->getNodeAttr($node_index, "type") == 'table') {
            $this->visited_list[] = $node_index;
            $table_name = $this->getNodeAttr($node_index, 'name');
            $this->debug("select from table = $table_name");
            $this->selectClause[] = $table_name.".*";
            $this->fromClause[] = $table_name;
            $this->whereClause = $this->getWhere($node_index, $table_name);
    		//$related_node_ids = $this->getJoinArray();
    		//foreach($related_node_ids as $node_id) {
            //    $this->nodeToSqlSelectQuery($node_id);
            //}
        }
    }
    
    // query the database with a select statment
    private function retrieveData() {
        $query = " select ".implode(", ", $this->selectClause);
        $query .= " from ".implode(", ", $this->fromClause);
        $query .= (!empty($this->whereClause))? " where ".implode(" and ", $this->whereClause): "";
        $query .= (!empty($this->orderbyClause))? " order by ".implode(", ", $this->orderbyClause): "";
        $this->debug_data("retrieveData = ", $query);
        $data = array();
        $resource = mysql_query($query);
        while($resource && $row = mysql_fetch_assoc($resource)) {
            $data[] = $row;
        }
        return $data;
    }
    
    private function nodeToSqlUpdateQuery($node_index) {
        if (!in_array($node_index, $this->visited_list)
        && $this->getNodeAttr($node_index, "type") == 'table') {
            $this->visited_list[] = $node_index;
            $table_name = $this->getNodeAttr($node_index, 'name');
            $this->debug("update table = $table_name");
            $this->UpdateClause = "update ".$table_name." ";
            $this->setClause = $this->getSetClause($node_index, $table_name);
            $this->whereClause = $this->getWhere($node_index, $table_name);
        }
    }
    // send an update query to the database
    private function executeUpdate() {
        $query = $this->UpdateClause;
        $query .= (!empty($this->setClause))? " set ".implode(", ", $this->setClause): "";
        $query .= (!empty($this->whereClause))? " where ".implode(" and ", $this->whereClause): "";
        $this->debug_data("executeUpdate = ", $query);
        $data = array();
        $resource = mysql_query($query);
        if($resource) {
            $data['nth_query_success'] = true;
            $data['nth_query_rows_affected'] = mysql_affected_rows();
        }
        else {
            $data['nth_query_success'] = false;
        }
        return $data;
    }

    private function nodeToSqlInsertQuery($node_index) {
        if (!in_array($node_index, $this->visited_list)
        && $this->getNodeAttr($node_index, "type") == 'table') {
            $this->visited_list[] = $node_index;
            $table_name = $this->getNodeAttr($node_index, 'name');
            $this->debug("insert into table = $table_name");
            $this->InsertClause = "insert into ".$table_name." ";
            $fieldAndValues = $this->getInsertFieldAndValuesClause($node_index, $table_name);
            $this->FieldsClause = $fieldAndValues['fields'];
            $this->ValuesClause = $fieldAndValues['values'];
        }
    }
    
    // send an insert query to the database
    private function executeInsert() {
        $query = $this->InsertClause;
        $query .= (!empty($this->FieldsClause))? " (".implode(", ", $this->FieldsClause).") ": "";
        $query .= (!empty($this->ValuesClause))? " values (".implode(", ", $this->ValuesClause).") ": "";
        $this->debug_data("executeInsert = ", $query);
        $data = array();
        $resource = mysql_query($query);
        if($resource) {
            $data['nth_query_success'] = true;
            $data['nth_query_rows_affected'] = mysql_affected_rows();
        }
        else {
            $data['nth_query_success'] = false;
        }
        return $data;
    }
    
    private function nodeToSqlDeleteQuery($node_index) {
        if (!in_array($node_index, $this->visited_list)
        && $this->getNodeAttr($node_index, "type") == 'table') {
            $this->visited_list[] = $node_index;
            $table_name = $this->getNodeAttr($node_index, 'name');
            $this->debug("insert into table = $table_name");
            $this->DeleteClause = "delete from ".$table_name." ";
            $this->whereClause = $this->getWhere($node_index, $table_name);
        }
    }
    
    // send an delete query to the database
    private function executeDelete() {
        $query = $this->DeleteClause;
        $query .= (!empty($this->whereClause))? " where ".implode(" and ", $this->whereClause): "";
        $this->debug_data("executeDelete = ", $query);
        $data = array();
        $resource = mysql_query($query);
        if($resource) {
            $data['nth_query_success'] = true;
            $data['nth_query_rows_affected'] = mysql_affected_rows();
        }
        else {
            $data['nth_query_success'] = false;
        }
        return $data;
    }
    
    private function getStartNodeIndex() {
    	return 0;
    }
    
    private function getWhere($node_index, $table_name) {
    	$data = array();
    	$whileEdge = $this->getOutEdges($node_index, "where");
    	if(!empty($whileEdge)) {
    		$to_node_index = $whileEdge[0]['to'];
    		$data[] = $this->getConditions($to_node_index, $table_name);
    	}
    	return $data;
    }
    
    private function getConditions($node_index, $table_name) {
    	$cond = array();
    	// branch on node type (field, conjuction)
        $field_name = $this->getNodeAttr($node_index, 'name');
        $conditionEdges = $this->getOutEdges($node_index, "constraint");
    	foreach($conditionEdges as $edge_index=>$edge_obj) {
    		$to_node_index = $edge_obj['to'];
    	    $conditional = $edge_obj['subtype'];
            $value = $this->getNodeDataAttr($to_node_index, $field_name);
    		$condition_str = $table_name.".".$field_name.$conditional.$value;
    		$cond[] = $condition_str;
    	}
        $conditional_str = " ".implode(" and ", $cond);
        $this->debug("added condition = $conditional_str");

    	return $conditional_str;
    }
    
    // return a list of node_indicies that are related to this table
    private function getJoinArray($node_index) {
    	$to_node_indcies = array();
    	$outEdges = $this->getOutEdges($node_index, "join");
    	foreach($outEdges as $edge_index=>$edge_obj) {
    		$to_node_indcies[] = $edge_obj['to'];
    	}
    	//var_dump($outEdges);
    	//echo " <br /> transition to node index $to_node_index";
    	return $to_node_indcies;
    }
    private function getSetClause($node_index, $table_name) {
    	$data = array();
    	$setEdge = $this->getOutEdges($node_index, "set_value");
    	if(!empty($setEdge)) {
    		$to_node_index = $setEdge[0]['to'];
    		$data[] = $this->getSetValue($to_node_index, $table_name);
    	}
    	return $data;
    }
    
    private function getSetValue($node_index, $table_name) {
    	$set_pairs = array();
    	// branch on node type (field, conjuction)
        $field_name = $this->getNodeAttr($node_index, 'name');
        $valueEdges = $this->getOutEdges($node_index, "get");
    	foreach($valueEdges as $edge_index=>$edge_obj) {
    		$to_node_index = $edge_obj['to'];
            $alias = $edge_obj['alias'];
            $value = $this->getNodeDataAttr($to_node_index, $alias);
    		$set_pairs_str = $table_name.".".$field_name." = '".$value."'";
    		$set_pairs[] = $set_pairs_str;
    	}
        $set_pairs_str = " ".implode(", ", $set_pairs);
        $this->debug("added set name value pair = $set_pairs_str");

    	return $set_pairs_str;
    }
    
    private function getInsertFieldAndValuesClause($node_index, $table_name) {
    	$setEdge = $this->getOutEdges($node_index, "set_value");
    	if(!empty($setEdge)) {
    		$to_node_index = $setEdge[0]['to'];
    		return $this->getInsertFieldAndValues($to_node_index, $table_name);
    	}
		return false;
    }
    
    private function getInsertFieldAndValues($node_index, $table_name) {
    	$insert_arr = array();
    	$insert_arr['fields'] = array();
    	$insert_arr['values'] = array();
        $field_name = $this->getNodeAttr($node_index, 'name');
        $valueEdges = $this->getOutEdges($node_index, "get");
    	foreach($valueEdges as $edge_index=>$edge_obj) {
    		$to_node_index = $edge_obj['to'];
            $alias = $edge_obj['alias'];
            $value = $this->getNodeDataAttr($to_node_index, $alias);
    		$insert_arr['fields'][] = $field_name;
			$insert_arr['values'][] = $value;
    	}

    	return $insert_arr;
    }
}

?>